﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Transactions;

namespace Species
{
    public class SpeciesDAL
    {
        public void AddContinent(Continent continent)
        {
            SpeciesDatabaseEntities context = new SpeciesDatabaseEntities();
            context.Continents.AddObject(continent);
            context.SaveChanges();
        }

        public void AddCountry(Country country) 
        {
            SpeciesDatabaseEntities context = new SpeciesDatabaseEntities();
            context.Countries.AddObject(country);
            context.SaveChanges();
        }

        public void AddSpeciesType(SpeciesType type)
        {
            SpeciesDatabaseEntities context = new SpeciesDatabaseEntities();
            context.SpeciesTypes.AddObject(type);
            context.SaveChanges();
        }

        public void AddAnimal(Species species, Animal animal)
        {
            SpeciesDatabaseEntities context = new SpeciesDatabaseEntities();
            context.Species.AddObject(species);
            context.Animals.AddObject(animal);
        }

        public void AddPlant(Species species, Plant plant)
        {
            SpeciesDatabaseEntities context = new SpeciesDatabaseEntities();
            context.Species.AddObject(species);
            context.Plants.AddObject(plant);
        }

        public void AddMushRoom(Species species, Mushroom mushroom)
        {
            SpeciesDatabaseEntities context = new SpeciesDatabaseEntities();
            context.Species.AddObject(species);
            context.Mushrooms.AddObject(mushroom);
        }

        public void UpdateContinent(Continent continent)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                context.Continents.Attach(continent);
                context.ObjectStateManager.GetObjectStateEntry(continent).SetModified();
                context.Refresh(RefreshMode.ClientWins, continent);
                context.SaveChanges();
            }
        }

        public void UpdateCountry(Country country)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                context.Countries.Attach(country);
                context.ObjectStateManager.GetObjectStateEntry(country).SetModified();
                context.Refresh(RefreshMode.ClientWins, country);
                context.SaveChanges();
            }
        }

        public void UpdateSpciesType(SpeciesType type)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                context.SpeciesTypes.Attach(type);
                context.ObjectStateManager.GetObjectStateEntry(type).SetModified();
                context.Refresh(RefreshMode.ClientWins, type);
                context.SaveChanges();
            }
        }

        public void UpdateSpecies(Species species)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                context.Species.Attach(species);
                context.ObjectStateManager.GetObjectStateEntry(species).SetModified();
                context.Refresh(RefreshMode.ClientWins, species);
                context.SaveChanges();
            }
        }

        public void UpdateAnimal(Animal animal)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                context.Animals.Attach(animal);
                context.ObjectStateManager.GetObjectStateEntry(animal).SetModified();
                context.Refresh(RefreshMode.ClientWins, animal);
                context.SaveChanges();
            }
        }

        public void UpdatePlant(Plant plant)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                context.Plants.Attach(plant);
                context.ObjectStateManager.GetObjectStateEntry(plant).SetModified();
                context.Refresh(RefreshMode.ClientWins, plant);
                context.SaveChanges();
            }
        }

        public void UpdateMushroom(Mushroom mushroom)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                context.Mushrooms.Attach(mushroom);
                context.ObjectStateManager.GetObjectStateEntry(mushroom).SetModified();
                context.Refresh(RefreshMode.ClientWins, mushroom);
                context.SaveChanges();
            }
        }

        public Continent ReadContinent(int continentId)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                return context.Continents.FirstOrDefault(c => c.ContinentId == continentId);
            }
        }

        public Country ReadCountry(int countryId) 
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                return context.Countries.FirstOrDefault(c => c.ContryId == countryId);
            }
        }

        public Species ReadSpecies(int speciesId)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                return context.Species.FirstOrDefault(spec => spec.SpeciesId == speciesId);
            }
        }

        public SpeciesType ReadSeciesType(int speciesTypeId)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                return context.SpeciesTypes.FirstOrDefault(specType =>
                    specType.SpeciesTypeId == speciesTypeId);
            }
        }

        public void DeleteContinent(int continentId)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                Continent continentToDelete = this.ReadContinent(continentId);
                context.Continents.DeleteObject(continentToDelete);
            }
        }

        public void DeleteCountry(int countryId)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                Country countryToDelete = this.ReadCountry(countryId);
                context.Countries.DeleteObject(countryToDelete);
            }
        }

        public void DeleteSpecies(int speciesId)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                Species speciesToDelete = this.ReadSpecies(speciesId);
                context.Species.DeleteObject(speciesToDelete);
            }
        }

        public void DeleteSpeciesType(int speciesType)
        {
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities())
            {
                SpeciesType specitesTypeToDelete = this.ReadSeciesType(speciesType);
                context.SpeciesTypes.DeleteObject(specitesTypeToDelete);
            }
        }

        public int DeletePoisonosMushroomsByContinent(int continentId) 
        {
            int mushroomsToDelete = 0;
            using (SpeciesDatabaseEntities context = new SpeciesDatabaseEntities()) 
            {
                using (TransactionScope scope = new TransactionScope()) 
                {
                    string query = "select COUNT(*) From Species where SpeciesTypeId = 2";
                    var result = context.ExecuteStoreQuery<int>(query);
                    int allPlants = result.FirstOrDefault();
                    query = string.Format("select COUNT(*) From Continents cont, " +
                        "Species spec join Mushrooms mush " +
                        "on spec.SpeciesId = mush.SpeciesId " +
                        "where mush.Poisonous = 'yes' and cont.ContinentId = {0}", continentId);
                    result = context.ExecuteStoreQuery<int>(query);
                    mushroomsToDelete = result.FirstOrDefault();
                    query = "select COUNT(*) From Species where SpeciesTypeId = 3";
                    result = context.ExecuteStoreQuery<int>(query);
                    int allMushrooms = result.FirstOrDefault();
                    if (allPlants > (allMushrooms - mushroomsToDelete))
                    {
                        throw new Exception("There will more plants than mushrooms");
                    }
                    else 
                    {
                        query = "delete from Species " +
                                "where SpeciesId in  "+
                                "( " +
	                                "select SpeciesId " +
	                                "from Mushrooms " +
	                                "where Poisonous = 'yes' "+
                                ")";
                        context.ExecuteStoreQuery<object>(query);
                        scope.Complete();
                        context.SaveChanges();
                        
                    }
                }
            }
            return mushroomsToDelete;
        }

        public IEnumerable<usp_GroupbyContinent_Result> 
            GroupSpeciesByContinent(string continentName) 
        {
            SpeciesDatabaseEntities context = new SpeciesDatabaseEntities();
            ObjectResult<usp_GroupbyContinent_Result> result = context.usp_GroupbyContinent(continentName);
            return result;
        }
    }
}


